package Dao;

import JDBCUtil.JDBCUtil;
import entity.User;

import javax.servlet.http.HttpServletRequest;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    JDBCUtil util = new JDBCUtil();

    public int add(User user, HttpServletRequest request){
        String sql = "insert into user (username, password, sex, email)" +
                "values (?, ?, ?, ?)";
        PreparedStatement ps = util.getPreparedStatement(sql, request);
        int result = 0;
        try {
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getSex());
            ps.setString(4, user.getEmail());
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            util.close(request);
        }
        return result;
    }

    public int add(User user){
        String sql = "insert into user (username, password, sex, email)" +
                "values (?, ?, ?, ?)";
        PreparedStatement ps = util.getPreparedStatement(sql);
        int result = 0;
        try {
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getSex());
            ps.setString(4, user.getEmail());
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            util.close();
        }
        return result;
    }

    public int delete(int userId){
        String sql = "delete from user where userid = ?";
        PreparedStatement ps = util.getPreparedStatement(sql);
        int result = 0;
        try {
            ps.setInt(1, userId);
            result = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            util.close();
        }
        return result;
    }

    public int update(User user){
        String sql = "update user set username = ?, password = ?, sex = ?, email = ? where userId = ?";
        PreparedStatement ps = util.getPreparedStatement(sql);
        int result = 0;
        try {
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getSex());
            ps.setString(4, user.getEmail());
            ps.setInt(5, user.getUserId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            util.close();
        }
        return result;
    }

    public List<User> findAll(){
        String sql = "select * from user";
        PreparedStatement ps = util.getPreparedStatement(sql);
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            rs = ps.executeQuery();
            while (rs.next()){
                int userId = rs.getInt("userid");
                String userName = rs.getString("username");
                String password = rs.getString("password");
                String sex = rs.getString("sex");
                String email = rs.getString("email");

                User user = new User(userId, userName, password, sex, email);
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            util.close(rs);
        }
        return list;
    }

    public int login(String userName, String password){

        String sql = "select count(*) from user where username = ? and password = ?";
        PreparedStatement ps = util.getPreparedStatement(sql);
        int result = 0;
        try {
            ps.setString(1, userName);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) result = rs.getInt("count(*)");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            util.close();
        }
        return result;
    }

}
